package com.zmh.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zmh.common.DateUtil;
import com.zmh.common.OperationUtil;
import com.zmh.model.book.BookFav;

/** 
 * BookLink书架操作
 * @author zhoumh
 * @version 创建时间：2017年10月12日 下午3:53:31 
 * @Description 
*/
@Repository
public class BookLinkDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	/**
	 * 查询书架的全部书籍信息
	 * @author zhoumh
	 * @version 创建时间：2017年8月15日 上午11:35:15
	 */
	public List<BookFav> queryBookByAll(){
		String sqlstr = "SELECT id, uuid, user, bid, bname, nstatus, nurl, rstatus, state, createtime, updatetime FROM booklink";
		List<BookFav> bookFavs = new ArrayList<>();
		bookFavs = jdbcTemplate.query(sqlstr,
				new RowMapper<BookFav>(){
				@Override
				public BookFav mapRow(ResultSet rs, int rowNum) throws SQLException {
					return objSet(rs, rowNum);
				}
				});
		System.out.println(bookFavs.size());
		return bookFavs;
	}
	/**
	 * 查询用户的书籍总数
	 * @author zhoumh
	 * @version 创建时间：2017年8月15日 上午11:35:15
	 */
	public int countBookFav(String uuid){
		String sqlstr = "SELECT count(id) FROM booklink WHERE user= ?";
		Object[] param = new Object[]{uuid};
		int count = jdbcTemplate.queryForObject(sqlstr,param,Integer.class);
		System.out.println(count);
		return count;
	}
	/**
	 * 根据USER分页查询书架的书籍信息
	 * @param 用户uuid,分页开始条数,分页结束条数
	 * @author zhoumh
	 * @version 创建时间：2017年8月17日 下午5:19:45
	 */
	public List<BookFav> queryBookByUser(String uuid,int begin,int end){
		String sqlstr = "SELECT id, uuid, user, bid, bname, nstatus, nurl, rstatus, state, createtime, updatetime FROM booklink WHERE user= ? LIMIT ?, ?";
		List<BookFav> bookFavs = new ArrayList<>();
		Object[] param = new Object[]{uuid,begin,end};
		bookFavs = jdbcTemplate.query(sqlstr, param, new RowMapper<BookFav>(){
				@Override
				public BookFav mapRow(ResultSet rs, int rowNum) throws SQLException {
					return objSet(rs, rowNum);
				}
				});
		return bookFavs;
	}
	/**
	 * 根据USER查询书架的书籍信息
	 * @param 用户uuid,分页开始条数,分页结束条数
	 * @author zhoumh
	 * @version 创建时间：2017年8月17日 下午5:19:45
	 */
	public Map<String, BookFav> queryBookByUserAll(String uuid){
		String sqlstr = "SELECT id, uuid, user, bid, bname, nstatus, nurl, rstatus, state, createtime, updatetime FROM booklink WHERE user= ?";
		List<BookFav> bookFavs = new ArrayList<>();
		Object[] param = new Object[]{uuid};
		bookFavs = jdbcTemplate.query(sqlstr, param, new RowMapper<BookFav>(){
				@Override
				public BookFav mapRow(ResultSet rs, int rowNum) throws SQLException {
					return objSet(rs, rowNum);
				}
				});
		Map<String, BookFav> map = new HashMap<String, BookFav>();
		for (int i = 0; i < bookFavs.size(); i++) {
			BookFav bookFav = bookFavs.get(i);
			map.put(bookFav.getBid(), bookFav);
		}
		return map;
	}
	/**
	 * 向书架中添加书籍
	 * @author zhoumh
	 * @version 创建时间：2017年9月20日 下午3:03:55
	 */
	public int insertBookFav(BookFav bookFav){
		String sqlstr = "INSERT INTO booklink (uuid, user, bid, bname, nstatus, nurl, rstatus) VALUES (?, ?, ?, ?, ?, ?, ?)";
		Object[] param = new Object[]{OperationUtil.getUUID(),bookFav.getUser(),bookFav.getBid(),bookFav.getBname(),bookFav.getNstatus(),bookFav.getNurl(),bookFav.getRstatus()};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
		
	}
	/**
	 * 修改书架中书籍信息
	 * @author zhoumh
	 * @version 创建时间：2017年9月20日 下午3:04:04
	 */
	public int updateBookByUuid(BookFav bookFav){
		String sqlstr = "update booklink set user=?, bid=?, bname=?, nstatus=?, nurl=?, rstatus=?, state=?, updatetime=? where uuid=?";
		Object[] param = new Object[]{bookFav.getUser(),bookFav.getBid(),bookFav.getBname(),bookFav.getNstatus(),bookFav.getNurl(),bookFav.getRstatus(),bookFav.getState(),DateUtil.getDateTime(),bookFav.getUuid()};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
	}
	/**
	 * 修改书架中书籍信息
	 * @author zhoumh
	 * @version 创建时间：2017年9月20日 下午3:04:04
	 */
	public int updateBookByBid(BookFav bookFav){
		String sqlstr = "update booklink set user=?, bname=?, nstatus=?, nurl=?, rstatus=?, state=?, updatetime=? where bid=?";
		Object[] param = new Object[]{bookFav.getUser(),bookFav.getBname(),bookFav.getNstatus(),bookFav.getNurl(),bookFav.getRstatus(),bookFav.getState(),DateUtil.getDateTime(),bookFav.getBid()};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
	}
	/**
	 * 删除书籍
	 * @author zhoumh
	 * @version 创建时间：2017年9月20日 下午3:04:17
	 */
	public int deleteBookByUuid(String uuid){
		String sqlstr = "delete from booklink where uuid = ?";
		Object[] param = new Object[]{uuid};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
	}
	/**
	 * 数据库查询结果映射
	 * @author zhoumh
	 * @version 创建时间：2017年8月15日 上午11:36:14
	 */
	private BookFav objSet(ResultSet rs, int rowNum) throws SQLException {
		BookFav bookFav = new BookFav();
		bookFav.setId(rs.getInt("id"));
		bookFav.setUuid(rs.getString("uuid"));
		bookFav.setUser(rs.getString("user"));
		bookFav.setBid(rs.getString("bid"));
		bookFav.setBname(rs.getString("bname"));
		bookFav.setRstatus(rs.getString("rstatus"));
		bookFav.setNstatus(rs.getString("nstatus"));
		bookFav.setNurl(rs.getString("nurl"));
		bookFav.setState(rs.getString("state"));
		bookFav.setCreateTime(rs.getString("createtime"));
		bookFav.setUpdateTime(rs.getString("updatetime"));
		return bookFav;
	}
}
